﻿#include "Sqlite.h"

Sqlite::Sqlite()
{

}

void Sqlite::setSqlData(QString sqlPath, QString panelName)
{
    m_sqlPath = sqlPath;
    m_panelName = panelName;
}

void Sqlite::setCreateTableData(QString sqlPath, QString panelName, int measureNumber, QString description)
{
    m_sqlPath = sqlPath;
    m_panelName = panelName;
    m_measureNumber = measureNumber;
    m_description = description;
}

bool Sqlite::createDataBaseFile()
{
    QFile file(m_sqlPath);
    if( ! file.exists())
    {
        if(! file.open( QIODevice::ReadWrite | QIODevice::Text) )
        {
            QMessageBox::about(NULL, QString::fromLocal8Bit("提示"), QString::fromLocal8Bit("数据库文件创建失败"));
            return false;
        }
        file.close();
    }
    return true;
}

bool Sqlite::createDataBaseTable()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    if (db.open())
    {
        qDebug() << "db.open ok";
        QSqlQuery query;
        QString sqlcommand = QString("CREATE TABLE 'panelinfo' ("
                                     "'PanelName' TEXT NOT NULL UNIQUE,"
                                     "'Description' TEXT,"
                                     "'Pairs' INTEGER NOT NULL"
                                     ")");
        qDebug() << sqlcommand;
        if(!query.exec(sqlcommand))
        {
            db.close();
            return false;
        }

        sqlcommand = QString("INSERT INTO panelinfo (PanelName, Description, Pairs) VALUES ('%1', '%2', %3)").arg(m_panelName)
                                                                                                       .arg(m_description)
                                                                                                       .arg(m_measureNumber);
        qDebug() << sqlcommand;
        if(!query.exec(sqlcommand))
        {
            db.close();
            return false;
        }

        sqlcommand = QString("CREATE TABLE 'MeasureSpec' ("
                             "'MeasureID' INTEGER,"
                             "'SIDE' BOOL,"
                             "'CL' REAL,"
                             "'UCL' REAL,"
                             "'LCL' REAL,"
                             "'USL' REAL,"
                             "'LSL' REAL,"
                             "'STARTx' REAL,"
                             "'STARTy' REAL,"
                             "'STARTz' REAL,"
                             "'STARTSCALE' REAL,"
                             "'STARTLENS' INTEGER,"
                             "'STARTINTENSITY_TOP' INTEGER,"
                             "'STARTINTENSITY_BOTTOM' INTEGER,"
                             "'STARTEXPOURETIME' INTEGER,"
                             "'ENDx' REAL,"
                             "'ENDy' REAL,"
                             "'ENDz' REAL,"
                             "'ENDSCALE' REAL,"
                             "'ENDLENS' INTEGER,"
                             "'ENDINTENSITY_TOP' INTEGER,"
                             "'ENDINTENSITY_BOTTOM' INTEGER,"
                             "'ENDEXPOURETIME' INTEGER"
                             ")");

        qDebug() << sqlcommand;
        if(!query.exec(sqlcommand))
        {
            db.close();
            return false;
        }

        sqlcommand = QString("CREATE TABLE 'MeasureData' ("
                             "'PanelID' INTEGER,"
                             "'Time' DATETIME");
        for(int i = 1; i <= m_measureNumber; i++)
        {
            sqlcommand += QString(", 'Measure%1' REAL").arg(i, 2, 10, QChar('0'));
        }
        sqlcommand += QString(")");
        qDebug() << sqlcommand;
        if(query.exec(sqlcommand))
        {
            db.close();
            return true;
        }
        else
        {
            db.close();
            return false;
        }
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}

bool Sqlite::writeSpec2Sqlite(MeasureSpecInfo measureSpecInfo)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("INSERT INTO MeasureSpec (MeasureID, SIDE, CL, UCL, LCL, USL, LSL, "
                                     "STARTx, STARTy, STARTz, STARTSCALE, STARTLENS, STARTINTENSITY_TOP, STARTINTENSITY_BOTTOM, STARTEXPOURETIME, "
                                     "ENDx, ENDy, ENDz, ENDSCALE, ENDLENS, ENDINTENSITY_TOP, ENDINTENSITY_BOTTOM, ENDEXPOURETIME) ");
        sqlcommand += QString("VALUES (");
        sqlcommand += QString::number(measureSpecInfo.MeasureID);
        sqlcommand += "," + QString::number(measureSpecInfo.side);
        sqlcommand += "," + QString::number(measureSpecInfo.CL);
        sqlcommand += "," + QString::number(measureSpecInfo.UCL);
        sqlcommand += "," + QString::number(measureSpecInfo.LCL);
        sqlcommand += "," + QString::number(measureSpecInfo.USL);
        sqlcommand += "," + QString::number(measureSpecInfo.LSL);

        sqlcommand += "," + QString::number(measureSpecInfo.startX);
        sqlcommand += "," + QString::number(measureSpecInfo.startY);
        sqlcommand += "," + QString::number(measureSpecInfo.startZ);
        sqlcommand += "," + QString::number(measureSpecInfo.startScale);
        sqlcommand += "," + QString::number(measureSpecInfo.startLens);
        sqlcommand += "," + QString::number(measureSpecInfo.startIntensity_top);
        sqlcommand += "," + QString::number(measureSpecInfo.startIntensity_bottom);
        sqlcommand += "," + QString::number(measureSpecInfo.startExpoureTime);

        sqlcommand += "," + QString::number(measureSpecInfo.endX);
        sqlcommand += "," + QString::number(measureSpecInfo.endY);
        sqlcommand += "," + QString::number(measureSpecInfo.endZ);
        sqlcommand += "," + QString::number(measureSpecInfo.endScale);
        sqlcommand += "," + QString::number(measureSpecInfo.endLens);
        sqlcommand += "," + QString::number(measureSpecInfo.endIntensity_top);
        sqlcommand += "," + QString::number(measureSpecInfo.endIntensity_bottom);
        sqlcommand += "," + QString::number(measureSpecInfo.endExpoureTime);

        sqlcommand += QString(")");
        query.exec(sqlcommand);
        db.close();
        return true;
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}

QString Sqlite::readDescription()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    QString description;
    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("SELECT * FROM panelinfo");
        query.exec(sqlcommand);
        while(query.next())
        {
            description = query.value(1).toString();
        }
        db.close();
        return description;
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return QString("false");
    }
}

bool Sqlite::readMeasureSpec(QList<MeasureSpecInfo> &list)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    MeasureSpecInfo info;
    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("SELECT * FROM MeasureSpec");
        query.exec(sqlcommand);
        while(query.next())
        {
            info.MeasureID = query.value(0).toInt();
            info.side = query.value(1).toBool();
            info.CL = query.value(2).toDouble();
            info.UCL = query.value(3).toDouble();
            info.LCL = query.value(4).toDouble();
            info.USL = query.value(5).toDouble();
            info.LSL = query.value(6).toDouble();

            info.startX = query.value(7).toDouble();
            info.startY = query.value(8).toDouble();
            info.startZ = query.value(9).toDouble();
            info.startScale = query.value(10).toDouble();
            info.startLens = query.value(11).toInt();
            info.startIntensity_top = query.value(12).toInt();
            info.startIntensity_bottom = query.value(13).toInt();
            info.startExpoureTime = query.value(14).toInt();

            info.endX = query.value(15).toDouble();
            info.endY = query.value(16).toDouble();
            info.endZ = query.value(17).toDouble();
            info.endScale = query.value(18).toDouble();
            info.endLens = query.value(19).toInt();
            info.endIntensity_top = query.value(20).toInt();
            info.endIntensity_bottom = query.value(21).toInt();
            info.endExpoureTime = query.value(22).toInt();
            list << info;
        }
        db.close();
        return true;
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}

bool Sqlite::readMeasureData(QList<QString> &dateTime, QList<QStringList> &measureData,int measurementNumber, int topN)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);
    QStringList data;
    QString time;
    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("SELECT * FROM MeasureData ORDER BY time DESC limit 0, %1").arg(topN);
        query.exec(sqlcommand);
        while(query.next())
        {
            time = query.value(1).toString();

            for(int i = 0; i < measurementNumber; i++)
            {
                data.append( query.value(i+2).toString() );
            }
            dateTime << time;
            measureData << data;
            data.clear();
        }
        db.close();
        return true;
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}

bool Sqlite::writeData2Sqlite(QString panelID ,int measurementNumber, QStringList measureData)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("INSERT INTO MeasureData (PanelID, Time");
        for(int i = 0; i < measurementNumber; i++)
        {
            sqlcommand += QString(", Measure%1").arg(i + 1, 2, 10, QChar('0'));
        }
        QDateTime dateTime(QDateTime::currentDateTime());
        sqlcommand += QString(") VALUES ('%1', '%2'").arg(panelID).arg(dateTime.toString("yyyy-MM-dd hh:mm:ss"));

        for(int i = 0; i < measurementNumber; i++)
        {
            sqlcommand += QString(", '%1'").arg(measureData[i]);
        }
        sqlcommand += QString(")");
        qDebug() << "Command: " <<  sqlcommand;
        if( query.exec(sqlcommand) )
        {
            db.close();
            return true;
        }
        else
        {
            db.close();
            QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库写入数据失败！"));
            return false;
        }
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}

bool Sqlite::clearMeasureSpec()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_sqlPath);

    if (db.open())
    {
        QSqlQuery query;
        QString sqlcommand = QString("delete from MeasureSpec");
        qDebug() << "Command: " <<  sqlcommand;
        if( query.exec(sqlcommand) )
        {
            db.close();
            return true;
        }
        else
        {
            db.close();
            QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库写入数据失败！"));
            return false;
        }
    }
    else
    {
        QMessageBox::warning(NULL, QString::fromLocal8Bit("警告！"), QString::fromLocal8Bit("数据库打开失败！"));
        return false;
    }
}
